/*
 * Created by SharpDevelop.
 * User: MyWife
 * Date: 11/12/2009
 * Time: 9:20 PM
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.Windows.Forms;
using popLib;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.IO;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace GenerateAccessTable
{
	/// <summary>
	/// Description of Database.
	/// </summary>
	public class Database
	{
		#region Access
		public static void ReadAccessDB(string db, string password, string storePath){
			string connection = "provider=Microsoft.Jet.OLEDB.4.0;data source="+db+";";
			if( StringLib.isValid(password) ) connection += "Jet OLEDB:Database Password="+password+";";
			
			using (OleDbConnection conn = new OleDbConnection(connection))
            {
				 conn.Open();
				 DataTable SchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
				 
				 for (int i = 0; i < SchemaTable.Rows.Count; i++)
					 getAccessTableInformation( SchemaTable.Rows[i].ItemArray[2].ToString(), conn, storePath );
			}		
		}
		
		private static void getAccessTableInformation( string table, OleDbConnection conn, string storePath ){
			string query = "SELECT * FROM ["+table.Trim()+"]";
			OleDbCommand cmd = new OleDbCommand(query, conn);
			
			OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
            DataTable schemaTable = rdr.GetSchemaTable();
                       
            
            string result = "";
            foreach( DataRow row in schemaTable.Rows )
            	//result += string.Format("Name={0}\tSize={1}\tType={2}\tIsKey={3}\r\n", row["ColumnName"], row["ColumnSize"], row["DataType"].ToString().Replace("System.",""), ((bool)row["IsKey"]) ? 1:0 );
            	//result += string.Format("{0}{1}[{2}]\t{3}\r\n", ((bool)row["IsKey"]) ? "#":"",  row["ColumnName"], row["ColumnSize"], row["DataType"].ToString().Replace("System.","") );
            	result += string.Format("{0}\t{1}\r\n", row["ColumnName"], row["DataType"].ToString().Replace("System.","") );
            result = (result+"\r\n").Replace("\r\n\r\n","");
            
            FileLib.writeFile( Path.Combine(storePath, table+".inf"), result );
		}
		#endregion
		
		#region MYSQL
		public static void ReadMySQLDB(string server, string username, string password, string database, string storePath){
			string connection = "SERVER={0};" +
				"DATABASE={3};" +
				"UID={1};" +
				"PASSWORD={2};";
					
			connection = string.Format( connection, server, username, password, database );
			
			using (MySqlConnection  conn = new MySqlConnection(connection))
            {
				 conn.Open();
				 DataTable SchemaTable = conn.GetSchema("Tables");
				 
				 for (int i = 0; i < SchemaTable.Rows.Count; i++)
					 getMySQLTableInformation( SchemaTable.Rows[i].ItemArray[2].ToString(), conn, storePath );
			}		
		}
		
		private static void getMySQLTableInformation( string table, MySqlConnection conn, string storePath ){
			string query = "SELECT * FROM "+table.Trim();
			MySqlCommand cmd = new MySqlCommand(query, conn);
			
			conn.Close();
			conn.Open();
			MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
            DataTable schemaTable = rdr.GetSchemaTable();
                       
            
            string result = "";
            foreach( DataRow row in schemaTable.Rows )
            	//result += string.Format("Name={0}\tSize={1}\tType={2}\tIsKey={3}\r\n", row["ColumnName"], row["ColumnSize"], row["DataType"].ToString().Replace("System.",""), ((bool)row["IsKey"]) ? 1:0 );
            	//result += string.Format("{0}{1}[{2}]\t{3}\r\n", ((bool)row["IsKey"]) ? "#":"",  row["ColumnName"], row["ColumnSize"], row["DataType"].ToString().Replace("System.","") );
            	result += string.Format("{0}\t{1}\r\n", row["ColumnName"], row["DataType"].ToString().Replace("System.","") );
            result = (result+"\r\n").Replace("\r\n\r\n","");
            
            FileLib.writeFile( Path.Combine(storePath, table+".inf"), result );
            
            
		} 
		
		#endregion
				
	}
}
